﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Linq;
using System.Windows.Forms;
using DevExpress.XtraEditors; 
using UFIDA.U8C.AnLianDB;
using UFIDA.U8.DB;
using UFIDA.U8.PUB;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using System.IO;
using NPOI.XSSF.UserModel;
using System.Data.SqlClient;
using UFIDA.U8C.AnLianDB.DAL;
using UFIDA.U8C.AnLianDB;
using Newtonsoft.Json.Linq;

namespace AnLianSysClient
{
    public partial class frmConstruction : DevExpress.XtraEditors.XtraForm
    {
        private string Psncode;
        private string psnName;
        const string regKey = "DevExpress\\XtraGrid\\Layouts\\Construction";
        private string _corp = "";
        private static string u8curl = common.getConfigInfo("u8curl");
        private static string glurl = u8curl + "/u8cloud/api/gl/voucher/insert";
        public frmConstruction()
        {
            InitializeComponent();
        }

         
             
        private void GdvConstruction_CustomDrawRowIndicator(object sender, DevExpress.XtraGrid.Views.Grid.RowIndicatorCustomDrawEventArgs e)
        {
            if (e.Info.IsRowIndicator && e.RowHandle >= 0)

                e.Info.DisplayText = (e.RowHandle + 1).ToString();

        }


        /// <summary>
        /// 成本计算存储过程
        /// </summary>
        /// <param name="year"></param>
        /// <param name="month"></param>
        /// <param name="connstr"></param>
        private void calcworksp(int year, int month, string corp, string connstr)
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(connstr))
                {
                    using (SqlCommand cmd = new SqlCommand())
                    {
                        conn.Open();
                        cmd.CommandType = System.Data.CommandType.StoredProcedure;
                        cmd.CommandText = "sp_cust_work_calc_U8C";
                        cmd.CommandTimeout = 10000;
                        cmd.Connection = conn;
                        SqlParameter para1 = new SqlParameter("@year", System.Data.SqlDbType.Int, 4);
                        para1.Value = year;
                        cmd.Parameters.Add(para1);
                        SqlParameter para2 = new SqlParameter("@month", System.Data.SqlDbType.Int, 4);
                        para2.Value = month;
                        cmd.Parameters.Add(para2);
                        SqlParameter para3 = new SqlParameter("@corp", System.Data.SqlDbType.VarChar, 50);
                        para3.Value = Convert.ToInt32(corp);
                        cmd.Parameters.Add(para3);
                        cmd.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception e)
            {
                string errMsg = "sp_cust_work_calc 执行存储过程失败" + e.Message;
                UFIDA.U8.PUB.LogHelper.WriteLine(errMsg);
                throw new Exception(errMsg);
            }

        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="year"></param>
        /// <param name="month"></param>
        /// <param name="connstr"></param>
        private void calcwork(int year, int month, string corp, string connstr)
        {
            try
            { 
                //取U8C项目当月施工
                bool bmonth = this.getProjectMonth(year, month, corp, connstr);
                if (!bmonth)
                {
                    return;
                }
                //取U8C项目成本上月余额
                bool bbalance = this.getProjectBalance(year, month, corp, connstr);
                if (!bbalance)
                {
                    return;
                }
                //计算数据
                this.calcworksp(year, month, corp, connstr);
                MessageBox.Show("计算完成"); 
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        private bool getProjectMonth(int year, int month, string corp, string connstr)
        {
            string sql = "delete custwork_curcb_period where iyear = " + year + " and imonth = " + month + " and corp = " + corp;
            UFIDA.U8.DB.DbHelperSQL.ExecuteSql(sql, connstr);
            sql = "select * from vw_cust_jobmonth where unitcode = '"+ corp +"' and iyear = " + year + "and imonth = " + month;
            DataTable dt = UFIDA.NC.DB.DbHelperSQL_Ora.QueryDTOra(sql, "custwork_curcb_period", UFIDA.U8C.AnLianDB.common.u8cconn());
            if (dt==null || dt.Rows.Count == 0)
            {
                MessageBox.Show("没有获取到当月施工成本");
                return false;
            }
            UFIDA.U8.DB.DbHelperSQL.ImportDataByDataTable2(dt, "custwork_curcb_period", UFIDA.U8C.AnLianDB.common.bankconn());

            return true;
        }

        private bool getProjectBalance(int year, int month, string corp, string connstr)
        {
            string sql = "delete custwork_balcb_period where iyear = " + year + " and imonth = " + month + " and corp = " + corp;
            UFIDA.U8.DB.DbHelperSQL.ExecuteSql(sql, connstr);
            //if (month == 1)
            //{
            //    month = 12;
            //    year -= 1;
            //}
            //else
            //{
            //    month -= 1;
            //}
            sql = "select unitcode, " + year + ", " + month + ", valuecode, valuename, subjcode, subjname, sum(debitamount- creditamount) as debitamount from vw_cust_jobbalance  where iyear < " + year + " or (iyear = " + year + " and imonth < " + month + ")  group by valuecode, valuename, subjcode, subjname,unitcode  ";
            DataTable dt = UFIDA.NC.DB.DbHelperSQL_Ora.QueryDTOra(sql, "custwork_balcb_period", UFIDA.U8C.AnLianDB.common.u8cconn());
            if (dt == null || dt.Rows.Count == 0)
            {
                MessageBox.Show("没有获取到上月施工成本余额");
                return false;
            }
            UFIDA.U8.DB.DbHelperSQL.ImportDataByDataTable2(dt, "custwork_balcb_period", UFIDA.U8C.AnLianDB.common.bankconn());

            return true;
        }

        private void BtnConfirm_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            if (cbcorp.Text.IsNullOrEmpty())
            {
                MessageBox.Show("请先选择公司！");
                return;
            }
            string dbgndate = edtBgndate.Text;
            string denddate = edtEnddate.Text;
            if (string.IsNullOrEmpty(dbgndate) || string.IsNullOrEmpty(denddate))
            {
                MessageBox.Show("请先选择开始结束日期");
                return;
            }

            if (MessageBox.Show("计算成本结转将清除本月及后续月份已计算数据，请确认继续", "警告", MessageBoxButtons.OKCancel) == DialogResult.OK)
            {
                int year = edtBgndate.DateTime.Year;
                int month = edtBgndate.DateTime.Month;               
                try
                {
                    bool bsync = UFIDA.U8C.AnLianDB.common.SyncJob();
                    this.calcwork(year, month, _corp, UFIDA.U8C.AnLianDB.common.bankconn());

                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                } 
            } 
        }

        private void BtnGL_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            if (cbcorp.Text.IsNullOrEmpty())
            {
                MessageBox.Show("请先选择公司！");
                return;
            }
            string dbgndate = edtBgndate.Text;
            string denddate = edtEnddate.Text;
            if (string.IsNullOrEmpty(dbgndate) || string.IsNullOrEmpty(denddate))
            {
                MessageBox.Show("请先选择开始结束日期");
                return;
            }
            if (MessageBox.Show("生成本月工程核算结转凭证，请确认继续", "警告", MessageBoxButtons.OKCancel) == DialogResult.OK)
            { 
                int year = edtBgndate.DateTime.Year;
                int month = edtBgndate.DateTime.Month;
                string corp = _corp;
                //生成凭证
                this.sendGL(year, month, corp, UFIDA.U8C.AnLianDB.common.bankconn()); 
            }
        }

        /// <summary>
        /// 生成结转凭证
        /// </summary>
        /// <param name="year"></param>
        /// <param name="month"></param>
        /// <param name="connstr"></param>
        private void sendGL(int year, int month, string corp, string connstr)
        {
            string errMsg = "";
            string resResult = "";
            try
            {
                DAL_cust_trans_work dal = new DAL_cust_trans_work();
                List<cust_trans_work> mlist = dal.GetModel(year, month, corp, connstr);
                if (mlist != null && mlist.Count > 0)
                {
                    string js = this.getGLjs(mlist);
                    if (js == "")
                    {
                        MessageBox.Show("凭证JS空,请检查");
                    }
                    bool bpost = common.PostU8C(Guid.NewGuid().ToString(), js, glurl, ref resResult, ref errMsg);
                    if (!bpost)
                    {
                        MessageBox.Show("生成凭证失败:" + errMsg); 
                    }
                    else
                    {
                        MessageBox.Show("生成凭证完成"); 
                    }
                    
                } 
            }
            catch (Exception e)
            {
                errMsg = "生成凭证失败" + e.Message;
                UFIDA.U8.PUB.LogHelper.WriteLine(errMsg);
                throw new Exception(errMsg);
            }
        }

        private string getGLjs(List<cust_trans_work> mlist)
        { 
            JArray items =new JArray();
            //借方
            foreach (cust_trans_work item in mlist)
            {
                JObject detail = new JObject();
                //收款付款
                detail["explanation"] = "结转已完工项目成本";
                //辅助核算
                detail["pk_accsubj"] = "640104" + item.ccode.Substring(item.ccode.Length - 2, 2);
                JArray ass = new JArray();
                ass.Add(new JObject()
                    {
                        { "checktypecode","J1Ass"},
                        { "checkvaluecode",item.citemcode}
                    });
                detail["ass"] = ass;
                detail["pk_currtype"] = "CNY";
                detail["debitamount"] = item.transAmount.ToString();
                detail["localdebitamount"] = item.transAmount.ToString();
                items.Add(detail);
            }
            //贷方
            foreach (cust_trans_work item in mlist)
            {
                JObject detail = new JObject();
                //收款付款
                detail["explanation"] = "结转已完工项目成本"; 
                //辅助核算
                detail["pk_accsubj"] = item.ccode;
                JArray ass = new JArray();
                ass.Add(new JObject()
                    {
                        { "checktypecode","J1Ass"},
                        { "checkvaluecode",item.citemcode}
                    });
                detail["ass"] = ass;
                detail["pk_currtype"] = "CNY";
                detail["creditamount"] = item.transAmount.ToString();
                detail["localcreditamount"] = item.transAmount.ToString();
                items.Add(detail);
            }

            JObject req = new JObject();
            JArray head = new JArray();
            JObject voucher = new JObject();
            voucher["pk_corp"] = _corp;
            voucher["pk_glorgbook"] = UFIDA.U8C.AnLianDB.common.GetU8CGlorgBook(_corp);
            voucher["pk_prepared"] = common.getConfigInfo("u8cgluser"); // "18606510717";
            voucher["pk_vouchertype"] = "记账"; 
            voucher["prepareddate"] = edtEnddate.Text;
            voucher["details"] = items;
            head.Add(voucher);
            req["voucher"] = head;
            return req.ToString();
             
        }

        private void BtnFind_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            this.Query();
        }

        private void Query()
        {
            if (cbcorp.Text.IsNullOrEmpty())
            {
                MessageBox.Show("请先选择公司！");
                return;
            }
            StringBuilder sql = new StringBuilder();
            sql.Append("select *, case iflag when 1 then '是' else '否' end cflag from  wazh_construction where 1=1 ");

            if (!string.IsNullOrEmpty(edtCode.Text))
            {
                sql.Append(" and project like '%" + edtCode.Text + "%'");
            }

            if (!string.IsNullOrEmpty(edtName.Text))
            {
                sql.Append(" and projectname like '%" + edtName.Text + "%'");
            }

            if (!string.IsNullOrEmpty(cbcorp.Text))
            {
                sql.Append(" and corp = '" + _corp + "'");
            }

            if (!string.IsNullOrEmpty(cbsync.Text))
            {
                if (cbsync.Text.Equals("是"))
                {
                    sql.Append(" and iflag = 1");
                }
                else
                {
                    sql.Append(" and iflag = 0");
                }
            }

            if (!string.IsNullOrEmpty(edtBgndate.Text))
            {
                sql.Append(" and ddate >='" + edtBgndate.Text + "'");
            }
            if (!string.IsNullOrEmpty(edtEnddate.Text))
            {
                sql.Append(" and ddate <='" + edtEnddate.Text + "'");
            }

            gdConstruction.DataSource = null;
            dsConstruction = DbHelperSQL.Query(sql.ToString(), "wazh_construction", UFIDA.U8C.AnLianDB.common.bankconn());
            bdsContruction.DataSource = dsConstruction;
            gdConstruction.DataSource = bdsContruction;
            bdsContruction.DataMember = "wazh_construction";


            StringBuilder sqlcb = new StringBuilder();
            sqlcb.Append("select * from vw_cust_custwork_detailinfo where 公司 = '"+ _corp +"' and 年度 = year('" + edtBgndate.Text + "') and 月 =month('" + edtBgndate.Text + "')");

            gdcreport.DataSource = null;
            DataTable dtreport = DbHelperSQL.QueryAsDT(sqlcb.ToString(), UFIDA.U8C.AnLianDB.common.bankconn());
            gdcreport.DataSource = dtreport;
            gdcreport.DataMember = dtreport.TableName; 
        }

        private void btnImportExcel_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            //
            if (cbcorp.Text.IsNullOrEmpty())
            {
                MessageBox.Show("请先选择公司！");
                return;
            }
            string dbgndate = edtBgndate.Text;
            string denddate = edtEnddate.Text;
            if (string.IsNullOrEmpty(dbgndate) || string.IsNullOrEmpty(denddate))
            {
                MessageBox.Show("请先选择开始结束日期");
                return;
            }

            string sql = "delete from wazh_construction where ddate >= '" + dbgndate + "' and ddate <='" + denddate + "'";
            string strExcelName = "";
            try
            {
                openFileDialog1.FileName = "";
                openFileDialog1.Filter = "All files (*.*)|*.*|Excel files (*.xls)|*.xls|Excel files (*.xlsx)|*.xlsx";
                openFileDialog1.FilterIndex = 3;
                openFileDialog1.RestoreDirectory = true;
                //导入临时表
                if (openFileDialog1.ShowDialog() == DialogResult.OK)
                {
                    //读取excel                        
                    strExcelName = this.openFileDialog1.FileName;

                    System.Data.DataTable dtfx = new System.Data.DataTable("ExcelTable");
                    dtfx = this.GetExcelToDataTableBySheet(strExcelName, "", true);
                    if (dtfx != null && dtfx.Rows.Count > 0)
                    {
                        DbHelperSQL.ExecuteSql(sql, common.bankconn());
                        DbHelperSQL.ImportDataByDataTable2(dtfx, "wazh_construction", UFIDA.U8C.AnLianDB.common.bankconn());
                        this.Query();
                    }
                }
            }
            catch (Exception se)
            {
                LogHelper.WriteLine(se.StackTrace);
                MessageBox.Show(se.Message);
            }
        }


        /// <summary>
        /// excel转换datatable
        /// </summary>
        /// <param name="FileFullPath"></param>
        /// <returns></returns>
        private System.Data.DataTable GetExcelToDataTableBySheet(string FileFullPath, string sheetName, bool isFirstRowColumn)
        {
            ISheet sheet = null;
            System.Data.DataTable data = new System.Data.DataTable();
            int startRow = 0;
            HSSFWorkbook HSFbook = null;
            XSSFWorkbook XSFbook = null;
            ICell cell = null;
            try
            {
                FileStream fs = new FileStream(FileFullPath, FileMode.Open, FileAccess.Read);
                if (FileFullPath.IndexOf(".xlsx") > 0)
                { // 2007版本
                    XSFbook = new XSSFWorkbook(fs);
                    if (!string.IsNullOrEmpty(sheetName))
                    {
                        sheet = XSFbook.GetSheet(sheetName);
                    }
                    else
                    {
                        sheet = XSFbook.GetSheetAt(0);
                    }
                }

                else if (FileFullPath.IndexOf(".xls") > 0) // 2003版本
                {
                    HSFbook = new HSSFWorkbook(fs);
                    if (!string.IsNullOrEmpty(sheetName))
                    {
                        sheet = HSFbook.GetSheet(sheetName);
                    }
                    else
                    {
                        sheet = HSFbook.GetSheetAt(0);
                    }
                }


                if (sheet != null)
                {
                    int rowCount = sheet.LastRowNum;
                    if (rowCount <= 1)
                    {
                        throw new Exception("行数不对，请检查是否有标题行及字段名行");
                    }
                    IRow captionrow = sheet.GetRow(0);
                    IRow firstRow = sheet.GetRow(1);
                    int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数
                    if (isFirstRowColumn)
                    {

                        for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                        {
                            DataColumn column = new DataColumn(firstRow.GetCell(i).StringCellValue);
                            data.Columns.Add(column);
                        }
                        startRow = firstRow.RowNum + 1;
                    }
                    else
                    {
                        startRow = sheet.FirstRowNum;
                    }
                    //最后一列的标号

                    for (int i = startRow; i <= rowCount; ++i)
                    {

                        IRow row = sheet.GetRow(i);
                        if (row == null) continue; //没有数据的行默认是null　　　　　　　

                        DataRow dataRow = data.NewRow();
                        for (int j = row.FirstCellNum; j < cellCount; ++j)
                        {
                            cell = row.GetCell(j);
                            if (cell != null)
                            {
                                switch (cell.CellType)
                                {
                                    case CellType.Blank:
                                        dataRow[j] = "";
                                        break;
                                    case CellType.Numeric:
                                        short format = cell.CellStyle.DataFormat;
                                        //对时间格式（2015.12.5、2015/12/5、2015-12-5等）的处理
                                        if (format == 14 || format == 31 || format == 57 || format == 58 || format == 177 || format == 181 || format == 179 || format == 178 || format == 176)
                                            dataRow[j] = cell.DateCellValue.ToString("yyyy-MM-dd");
                                        else
                                            dataRow[j] = cell.NumericCellValue;
                                        break;
                                    case CellType.String:
                                        dataRow[j] = cell.StringCellValue;
                                        break;
                                }
                            }
                            else
                            {
                                dataRow[j] = "";
                            }
                            //if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null
                            //    dataRow[j] = row.GetCell(j).ToString();
                        }
                        data.Rows.Add(dataRow);
                    }
                }
                return data;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                //Console.WriteLine("Exception: " + ex.Message);
                return null;
            }

        }

        private void btnImportHX_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            if (cbcorp.Text.IsNullOrEmpty())
            {
                MessageBox.Show("请先选择公司！");
                return;
            } 
            string dbgndate = edtBgndate.Text;
            string denddate = edtEnddate.Text;
            if (string.IsNullOrEmpty(dbgndate) || string.IsNullOrEmpty(denddate))
            {
                MessageBox.Show("请先选择开始结束日期");
                return;
            }
            string sqld = "delete from wazh_construction where ddate >= '" + dbgndate + "' and ddate <='" + denddate + "'";
            
            StringBuilder sql = new StringBuilder();
            sql.Append("select project, projectname, ddate ,  0 as id, 0 as iflag,  corp,  iaddqty, ireqty  from  wazh_construction where iflag = 0 ");
            try
            {
                dsConstruction = UFIDA.MYSQL.DB.DbHelperSQL.Query(sql.ToString(), "Contruction", UFIDA.U8C.AnLianDB.common.mysqlconn());

                if (dsConstruction != null && dsConstruction.Tables.Count > 0 && dsConstruction.Tables[0].Rows.Count > 0)
                {
                    DbHelperSQL.ExecuteSql(sqld, common.bankconn());
                    DbHelperSQL.ImportDataByDataTable(dsConstruction.Tables[0], "wazh_construction", UFIDA.U8C.AnLianDB.common.bankconn());
                    List<UFIDA.MYSQL.DB.CommandInfo> cmdlist = new List<UFIDA.MYSQL.DB.CommandInfo>();
                    foreach (DataRow item in dsConstruction.Tables[0].Rows)
                    {
                        cmdlist.Add(new UFIDA.MYSQL.DB.CommandInfo("update wazh_construction set iflag = 1 where id = " + item["id"].ToString(), null));
                    }
                    UFIDA.MYSQL.DB.DbHelperSQL.ExecuteSqlTran(cmdlist, UFIDA.U8C.AnLianDB.common.mysqlconn());
                    MessageBox.Show("导入海象数据完成");
                }
                else
                {
                    MessageBox.Show("没有查询到海象数据");
                }
                
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            } 
        }

        private void btnExcel_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            try
            {
                SaveFileDialog saveDialog = new SaveFileDialog();
                saveDialog.DefaultExt = "xlsx";
                saveDialog.Filter = "Excel文件|*.xlsx";
                saveDialog.FileName = "工程施工数据.xlsx";
                saveDialog.ShowDialog();
                string saveFileName = saveDialog.FileName;
                if (saveFileName.IndexOf(":") < 0) return; //被点了取消

                ExcelAdep.ExportExcel(gdvConstruction, true, saveFileName);

                MessageBox.Show("文件： " + saveFileName + ".xls 保存成功", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void frmConstruction_Load(object sender, EventArgs e)
        {
            //
            gdConstruction.MainView.RestoreLayoutFromRegistry(regKey);//读取（还原）布局 
            this.AddCorp();
        }

        private void AddCorp()
        {
            dscorp = UFIDA.U8C.AnLianDB.common.GetU8CCorp();
            if (dscorp!=null && dscorp.Tables.Count > 0 && dscorp.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in dscorp.Tables[0].Rows)
                {
                    cbcorp.Properties.Items.Add(dr["unitname"].ToString());
                }
            }
        }

        private void frmConstruction_FormClosed(object sender, FormClosedEventArgs e)
        {
            gdConstruction.MainView.SaveLayoutToRegistry(regKey); //保存布局
        }

        private void cbcorp_SelectedIndexChanged(object sender, EventArgs e)
        {
            string unitname = cbcorp.Text;
            if (string.IsNullOrEmpty(unitname))
            {
                return;
            }
            dscorp.Tables[0].DefaultView.RowFilter = "unitname = '" + unitname + "'";
            _corp = dscorp.Tables[0].DefaultView[0]["unitcode"].ObjToString();
        }

        private void btnExcelCB_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            try
            {
                SaveFileDialog saveDialog = new SaveFileDialog();
                saveDialog.DefaultExt = "xlsx";
                saveDialog.Filter = "Excel文件|*.xlsx";
                saveDialog.FileName = "成本计算结果数据.xlsx";
                saveDialog.ShowDialog();
                string saveFileName = saveDialog.FileName;
                if (saveFileName.IndexOf(":") < 0) return; //被点了取消

                ExcelAdep.ExportExcel(grvreport, true, saveFileName);

                MessageBox.Show("文件： " + saveFileName + ".xls 保存成功", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        } 
    }
}